package dbx;

import stdx.Utils;
import stdx.ResPool;

import java.sql.*;
import java.util.Map;
import java.io.Closeable;
import java.util.HashMap;
import java.util.ArrayList;
import java.lang.reflect.Field;

public class DBConnect implements ResPool.Resource{
	public static class ResultLoop{
		protected ResultSetMetaData meta;
        protected Map<String, Integer> idxmap;

		public void next(String[] row){
		}
		public int getColumnIndex(String name){
		    name = name.toLowerCase();

		    if (idxmap == null){
                int res = -1;

		        idxmap = new HashMap<String, Integer>();

		        try {
                    int len = meta.getColumnCount();

                    for (int i = 0; i < len; i++){
                        String key = getName(i).toLowerCase();

                        if (key.equals(name)) res = i;

                        idxmap.put(key, i);
                    }
                }
                catch (Exception e){
                    e.printStackTrace();
                }

                return res;
            }

            Integer res = idxmap.get(name);

		    if (res == null) return -1;

            return res;
        }
        public Object initObject(Object obj, String[] row){
		    if (obj == null) return obj;

            Field[] fields = obj.getClass().getDeclaredFields();

            try {
                for (Field field : fields){
                    int index = getColumnIndex(field.getName());

                    if (index >= 0) Utils.InitFieldValue(field, obj, row[index]);
                }
            }
            catch (Exception e){
                e.printStackTrace();
            }

            return obj;
        }
        public String getName(int index) throws SQLException{
            return meta.getColumnName(index + 1);
        }
	}

    protected ResPool pool = null;
    protected Exception error = null;
    protected Connection conn = null;
	protected static Pool defaultpool = new Pool();
    protected static HashMap<String, Pool> poolmap = new HashMap<String, Pool>();

	public static class Config{
		public String url = "";
		public String user = "";
		public String driver = "";
		public String passwd = "";
	}

	public static class Pool extends ResPool {
		Config cfg = new Config();

        public DBConnect get() throws Exception{
            return (DBConnect)super.get();
        }
		public Resource create() throws Exception{
		    DBConnect conn = new DBConnect();
            return conn.connect(cfg);
        }
        public boolean checkSuccess(Closeable conn){
            DBConnect tmp = (DBConnect)(conn);
            if (tmp == null) return false;
            return tmp.error == null;
        }
		public Pool init(Config cfg, int maxlen, int timeout){
		    this.destroy();
			this.cfg = cfg;
			this.setLength(maxlen);
			this.setTimeout(timeout);
			return this;
		}
	}

	public void close(){
		if (pool == null){
			Utils.Close(conn);
            conn = null;
		}
		else{
			pool.release(this);
		}

		error = null;
	}
    public void setPool(ResPool pool){
        this.pool = pool;
    }
	public DBConnect connect(Config cfg) throws Exception{
		close();

		try{
			conn = Connect(cfg);
		}
		catch(Exception e){
			error = e;
			throw e;
		}

		return this;
	}
	public void commit() throws SQLException{
        try{
            conn.commit();
        }
        catch(Exception e){
            error = e;
            throw e;
        }
    }
    public void rollback() throws SQLException{
        try{
            conn.rollback();
        }
        catch(Exception e){
            error = e;
            throw e;
        }
    }
    public boolean getAutoCommit() throws SQLException{
        try{
            return conn.getAutoCommit();
        }
        catch(SQLException e){
            error = e;
            throw e;
        }
    }
    public void setAutoCommit(boolean flag) throws SQLException{
        try{
            conn.setAutoCommit(flag);
        }
        catch(SQLException e){
            error = e;
            throw e;
        }
    }
	public int execute(String sqlcmd, Object...args) throws SQLException{
		int res = 0;
		PreparedStatement stmt = null;

		error = null;

		try{
			stmt = conn.prepareStatement(sqlcmd);
			Bind(stmt, args);
			stmt.addBatch();
			stmt.execute();
			res = stmt.getUpdateCount();
		}
		catch(SQLException e){
			error = e;
			throw e;
		}
		finally{
			Utils.Close(stmt);
		}

		return res;
	}
    public int query(ResultLoop loop, String sqlcmd, Object...args) throws SQLException{
        int res = 0;
        ResultSet rs = null;
        PreparedStatement stmt = null;

        error = null;

        try{
            stmt = conn.prepareStatement(sqlcmd);
            Bind(stmt, args);
            stmt.addBatch();
            rs = stmt.executeQuery();
            loop.meta = rs.getMetaData();
            String[] row = new String[loop.meta.getColumnCount()];

            while (rs.next()){
                for (int i = 0; i < row.length; i++){
                    if ((row[i] = rs.getString(i + 1)) == null) row[i] = "";
				}
				loop.next(row);
                res++;
            }
        }
        catch(SQLException e){
            error = e;
            throw e;
        }
        finally{
            Utils.Close(rs);
            Utils.Close(stmt);
        }

        return res;
    }
    public <T> T select(Class<T> clazz, String sqlcmd, Object...args) throws SQLException, InstantiationException, IllegalAccessException{
        ArrayList<T> vec = selectList(clazz, sqlcmd, args);
        return vec.isEmpty() ? null : vec.get(0);
    }
    public <T> ArrayList<T> selectList(final Class<T> clazz, String sqlcmd, Object...args) throws SQLException, InstantiationException, IllegalAccessException{
        final ArrayList<T> res = new ArrayList<T>();

        query(new ResultLoop(){
            public void next(String[] row){
                T obj = Utils.GetSimpleObject(clazz, row[0]);

                if (obj == null){
                    try{
                        initObject(obj = clazz.newInstance(), row);
                    }
                    catch(Exception e){
                        e.printStackTrace();
                    }
                }

                res.add(obj);
            }
        }, sqlcmd, args);

        return res;
    }

	public static Pool GetPool(){
		return defaultpool;
	}
	public static Pool GetPool(String name){
		synchronized(poolmap){
			Pool pool = poolmap.get(name);

			if (pool == null){
				pool = new Pool();
				poolmap.put(name, pool);
			}

			return pool;
		}
	}
	public static DBConnect Connect() throws Exception{
		return defaultpool.get();
	}
	public static DBConnect Connect(String name) throws Exception{
		return GetPool(name).get();
	}
	public static Connection Connect(Config cfg) throws Exception{
		String driver = cfg.driver;

		if (driver.isEmpty()){
			String name = cfg.url.toLowerCase();

			if (name.indexOf("mysql") >= 0){
				driver = "com.mysql.cj.jdbc.Driver";
			}
			else if (name.indexOf("oracle") >= 0){
				driver = "oracle.jdbc.driver.OracleDriver";
			}
			else if (name.indexOf("postgres") >= 0){
				driver = "org.postgresql.Driver";
			}
			else if (name.indexOf("sqlite") >= 0){
				driver = "org.sqlite.JDBC";
			}
			
			cfg.driver = driver;
		}

		try {
            Class.forName(driver);
        }
        catch (ClassNotFoundException e){
            if (driver.equals("com.mysql.cj.jdbc.Driver")){
                Class.forName(driver = "com.mysql.jdbc.Driver");
            }
            else{
                throw e;
            }
        }

		Connection conn = null;

		if (driver.toLowerCase().indexOf("sqlite") < 0){
			conn = DriverManager.getConnection(cfg.url, cfg.user, cfg.passwd);
		}
		else{
			conn = DriverManager.getConnection(cfg.url);
		}

		conn.setAutoCommit(true);

		return conn;
	}
    public static void Bind(PreparedStatement stmt, Object...args) throws SQLException{
        int idx = 1;

        for (Object item : args){
            if (item instanceof String){
                stmt.setString(idx++, (String)(item));
            }
            else if (item instanceof Integer){
                stmt.setInt(idx++, (Integer)(item));
            }
            else if (item instanceof Double){
                stmt.setDouble(idx++, (Double)(item));
            }
            else if (item instanceof Date){
                stmt.setDate(idx++, (Date)(item));
            }
            else if (item instanceof Long){
                stmt.setLong(idx++, (Long)(item));
            }
            else if (item instanceof Short){
                stmt.setShort(idx++, (Short)(item));
            }
            else if (item instanceof Float){
                stmt.setFloat(idx++, (Float)(item));
            }
            else if (item instanceof byte[]){
                stmt.setBytes(idx++, (byte[])(item));
            }
            else{
                stmt.setString(idx++, String.valueOf(item));
            }
        }
    }
}
